<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>话单查询系统</title>
    <style>
        body {
            font-family: 'Microsoft YaHei', Arial, sans-serif;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            margin: 0;
            padding: 20px;
            min-height: 100vh;
        }
        .container {
            max-width: 1200px;
            margin: 0 auto;
            background: white;
            border-radius: 15px;
            box-shadow: 0 20px 40px rgba(0,0,0,0.1);
        }
        .header {
            background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);
            color: white;
            padding: 30px;
            text-align: center;
            border-radius: 15px 15px 0 0;
        }
        .content {
            padding: 40px;
        }
        .form-group {
            margin-bottom: 20px;
        }
        .form-group label {
            display: block;
            margin-bottom: 8px;
            font-weight: bold;
        }
        .form-group input {
            width: 100%;
            padding: 12px;
            border: 2px solid #e1e5e9;
            border-radius: 8px;
            font-size: 16px;
        }
        .form-group textarea {
            width: 100%;
            padding: 12px;
            border: 2px solid #e1e5e9;
            border-radius: 8px;
            font-size: 14px;
            font-family: 'Courier New', monospace;
            resize: vertical;
        }
        .form-row {
            display: grid;
            grid-template-columns: 1fr 1fr;
            gap: 20px;
        }
        .btn {
            background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);
            color: white;
            border: none;
            padding: 15px 30px;
            border-radius: 8px;
            font-size: 16px;
            cursor: pointer;
            width: 100%;
        }
        .help-text {
            background: #e3f2fd;
            padding: 15px;
            border-radius: 8px;
            margin-bottom: 20px;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        th, td {
            padding: 12px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }
        th {
            background: #f8f9fa;
            font-weight: bold;
        }
        .error {
            background: #ffebee;
            color: #c62828;
            padding: 15px;
            border-radius: 8px;
            margin-bottom: 20px;
        }
        .success {
            background: #e8f5e8;
            color: #2e7d32;
            padding: 15px;
            border-radius: 8px;
            margin-bottom: 20px;
        }
        .group-header {
            background-color: #e3f2fd;
            font-weight: bold;
            padding: 12px;
            border-bottom: 2px solid #2196f3;
            cursor: pointer;
            user-select: none;
            transition: background-color 0.2s;
        }
        .group-header:hover {
            background-color: #bbdefb;
        }
        .group-header .toggle-icon {
            display: inline-block;
            margin-right: 8px;
            transition: transform 0.2s;
        }
        .group-header.collapsed .toggle-icon {
            transform: rotate(-90deg);
        }
        .group-content {
            display: block;
        }
        .group-content.collapsed {
            display: none;
        }
        .export-buttons {
            margin: 20px 0;
            display: flex;
            gap: 10px;
        }
        .export-btn {
            background: #4caf50;
            color: white;
            border: none;
            padding: 10px 20px;
            border-radius: 5px;
            cursor: pointer;
            font-size: 14px;
        }
        .export-btn:hover {
            background: #45a049;
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <h1>📞 话单查询系统</h1>
            <p>通过被叫号码查询通话记录</p>
        </div>
        
        <div class="content">
            <!-- 配置界面 -->
            <div id="configSection">
                <h2>⚙️ 系统配置</h2>
                <div class="help-text">
                    <h3>📋 配置说明</h3>
                    <p>请先填写SSH和数据库配置信息，然后点击"连接数据库"按钮建立连接。</p>
                </div>
                
                <form id="configForm">
                    <h3>🔐 SSH配置</h3>
                    <div class="form-row">
                        <div class="form-group">
                            <label for="sshHost">SSH主机地址 *</label>
                            <input type="text" id="sshHost" placeholder="例如: 192.168.1.100" required>
                        </div>
                        <div class="form-group">
                            <label for="sshPort">SSH端口 *</label>
                            <input type="number" id="sshPort" value="22" required>
                        </div>
                    </div>
                    <div class="form-row">
                        <div class="form-group">
                            <label for="sshUsername">SSH用户名 *</label>
                            <input type="text" id="sshUsername" placeholder="例如: root" required>
                        </div>
                        <div class="form-group">
                            <label for="sshPassword">SSH密码 *</label>
                            <input type="password" id="sshPassword" placeholder="请输入SSH密码" required>
                        </div>
                    </div>
                    
                    <h3>🗄️ 数据库配置</h3>
                    <div class="form-row">
                        <div class="form-group">
                            <label for="dbHost">数据库主机 *</label>
                            <input type="text" id="dbHost" value="127.0.0.1" required>
                        </div>
                        <div class="form-group">
                            <label for="dbPort">数据库端口 *</label>
                            <input type="number" id="dbPort" value="3306" required>
                        </div>
                    </div>
                    <div class="form-row">
                        <div class="form-group">
                            <label for="dbName">数据库名称 *</label>
                            <input type="text" id="dbName" placeholder="例如: vos3000" required>
                        </div>
                        <div class="form-group">
                            <label for="dbUser">数据库用户名 *</label>
                            <input type="text" id="dbUser" placeholder="例如: root" required>
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="dbPassword">数据库密码</label>
                        <input type="password" id="dbPassword" placeholder="请输入数据库密码（可为空）">
                    </div>
                    
                    <button type="submit" class="btn" id="connectBtn">🔗 连接数据库</button>
                </form>
                
                <div id="connectionStatus" style="display: none;">
                    <div class="success">
                        <h3>✅ 连接成功</h3>
                        <p>数据库连接已建立，现在可以进行查询操作。</p>
                        <button class="btn" onclick="showQuerySection()">🔍 开始查询</button>
                    </div>
                </div>
            </div>
            
            <!-- 查询界面 -->
            <div id="querySection" style="display: none;">
                <h2>🔍 话单查询</h2>
                <div class="help-text">
                    <h3>📋 查询模式说明</h3>
                    <ul>
                        <li><strong>被叫查询</strong>: 查询被叫号码的通话记录</li>
                        <li><strong>主叫查询</strong>: 查询主叫号码的通话记录</li>
                        <li><strong>复合查询</strong>: 同时指定主叫、被叫和日期条件</li>
                        <li><strong>*147*5846</strong> → 包含147和5846的号码</li>
                        <li><strong>*14708637862</strong> → 以14708637862结尾的号码</li>
                        <li><strong>147*</strong> → 以147开头的号码</li>
                        <li><strong>1475846</strong> → 自动生成147%5846 (前三后四)</li>
                        <li><strong>批量查询</strong> → 每行一个查询条件，可指定不同条件</li>
                    </ul>
                    <h4>📝 查询格式示例：</h4>
                    <pre style="background: #f5f5f5; padding: 10px; border-radius: 5px; font-size: 12px;">
# 被叫查询
1475846,20250727
1475847,20250728

# 主叫查询 (使用@前缀)
@1475846,20250727
@1475847,20250728

# 复合查询 (主叫@被叫,日期)
@1475846@1475847,20250727
@1475846@1475848,20250728

# 混合查询
1475846,20250727
@1475847,20250728
@1475846@1475847,20250729</pre>
                </div>
                
                <form id="queryForm">
                    <div class="form-group">
                        <label for="queryInput">查询内容 *</label>
                        <textarea id="queryInput" rows="8" placeholder="格式：查询条件,日期&#10;例如：&#10;1475846,20250727 (被叫查询)&#10;@1475846,20250727 (主叫查询)&#10;@1475846@1475847,20250727 (复合查询)&#10;&#10;说明：&#10;- 每行一个查询&#10;- 格式：查询条件,日期（日期可选）&#10;- 日期格式：YYYYMMDD&#10;- @前缀表示主叫查询&#10;- 可同时指定主叫和被叫" required></textarea>
                        <small style="color: #666; font-size: 12px;">支持被叫、主叫和复合查询，@前缀表示主叫，可同时指定主叫和被叫号码，支持批量查询</small>
                    </div>
                    <button type="submit" class="btn" id="queryBtn">🔍 开始查询</button>
                </form>
                
                <div id="message"></div>
                <div id="results" style="display: none;">
                    <h2>📊 查询结果</h2>
                    <div id="resultsCount"></div>
                    <div id="queryTime" style="color: #666; font-size: 14px; margin-bottom: 10px;"></div>
                    <div class="export-buttons">
                        <button class="export-btn" onclick="exportToCSV()">📄 导出CSV</button>
                        <button class="export-btn" onclick="exportToExcel()">📊 导出Excel</button>
                        <button class="export-btn" onclick="expandAllGroups()">📖 展开全部</button>
                        <button class="export-btn" onclick="collapseAllGroups()">📕 折叠全部</button>
                    </div>
                    <div id="groupedResults"></div>
                </div>
            </div>
        </div>
    </div>

    <script>
        document.getElementById('configForm').addEventListener('submit', function(e) {
            e.preventDefault();
            
            const sshHost = document.getElementById('sshHost').value;
            const sshPort = document.getElementById('sshPort').value;
            const sshUsername = document.getElementById('sshUsername').value;
            const sshPassword = document.getElementById('sshPassword').value;
            const dbHost = document.getElementById('dbHost').value;
            const dbPort = document.getElementById('dbPort').value;
            const dbName = document.getElementById('dbName').value;
            const dbUser = document.getElementById('dbUser').value;
            const dbPassword = document.getElementById('dbPassword').value;

            if (!sshHost || !sshUsername || !dbName || !dbUser) {
                showMessage('请填写所有SSH和数据库配置信息', 'error');
                return;
            }

            const config = {
                ssh_host: sshHost,
                ssh_port: sshPort,
                ssh_username: sshUsername,
                ssh_password: sshPassword,
                db_host: dbHost,
                db_port: dbPort,
                db_name: dbName,
                db_user: dbUser,
                db_password: dbPassword
            };

            const connectBtn = document.getElementById('connectBtn');
            connectBtn.disabled = true;
            connectBtn.textContent = '⏳ 连接中...';
            showMessage('正在连接数据库...', 'success');

            // 调用真实的配置接口
            fetch('/api/connect', {
                method: 'POST',
                headers: {'Content-Type': 'application/json'},
                body: JSON.stringify(config)
            })
            .then(response => response.json())
            .then(data => {
                if (data.success) {
                    showMessage(data.message, 'success');
                    document.getElementById('connectionStatus').style.display = 'block';
                    document.getElementById('configForm').reset(); // 清空配置表单
                } else {
                    showMessage(data.error, 'error');
                }
            })
            .catch(error => {
                showMessage('连接失败，请检查网络连接和配置信息', 'error');
            })
            .finally(() => {
                connectBtn.disabled = false;
                connectBtn.textContent = '🔗 连接数据库';
            });
        });

        document.getElementById('queryForm').addEventListener('submit', function(e) {
            e.preventDefault();
            
            const queryInput = document.getElementById('queryInput').value.trim();
            const queryBtn = document.getElementById('queryBtn');
            
            if (!queryInput) {
                showMessage('请输入查询内容', 'error');
                return;
            }
            
            const queries = queryInput.split('\n').map(q => q.trim()).filter(q => q);
            if (queries.length === 0) {
                showMessage('请输入有效的查询内容', 'error');
                return;
            }

            const patterns = [];
            const dates = [];
            const queryConditions = []; // 新增：查询条件对象数组
            
            queries.forEach(q => {
                const parts = q.split(',');
                if (parts.length > 0) {
                    let queryCondition = parts[0];
                    let date = '';
                    
                    if (parts.length > 1) {
                        date = parts[1];
                    }
                    
                    // 解析查询条件
                    const condition = {
                        caller: null,
                        callee: null,
                        date: date
                    };
                    
                    // 检查是否包含主叫条件（@前缀）
                    if (queryCondition.includes('@')) {
                        const segments = queryCondition.split('@');
                        if (segments.length >= 3) {
                            // 格式：@主叫@被叫
                            // segments[0] = "" (空，因为以@开头)
                            // segments[1] = 主叫号码
                            // segments[2] = 被叫号码
                            if (segments[1]) {
                                condition.caller = segments[1];
                            }
                            if (segments[2]) {
                                condition.callee = segments[2];
                            }
                        } else if (segments.length >= 2) {
                            // 格式：@主叫 或 被叫@主叫
                            if (queryCondition.startsWith('@')) {
                                // @主叫格式
                                if (segments[1]) {
                                    condition.caller = segments[1];
                                }
                            } else {
                                // 被叫@主叫格式
                                if (segments[0]) {
                                    condition.callee = segments[0];
                                }
                                if (segments[1]) {
                                    condition.caller = segments[1];
                                }
                            }
                        }
                    } else {
                        // 没有@前缀，默认为被叫查询
                        condition.callee = queryCondition;
                    }
                    
                    patterns.push(queryCondition);
                    dates.push(date); // 修复：确保日期被添加到dates数组
                    queryConditions.push(condition);
                }
            });

            if (patterns.length === 0) {
                showMessage('请输入有效的查询条件', 'error');
                return;
            }
            
            // 记录查询开始时间
            const queryStartTime = new Date();
            
            queryBtn.disabled = true;
            queryBtn.textContent = '⏳ 查询中...';
            showMessage(`正在查询 ${patterns.length} 个条件，请稍候...`, 'success');
            
            // 批量查询
            fetch('/api/batch_query', {
                method: 'POST',
                headers: {'Content-Type': 'application/json'},
                body: JSON.stringify({
                    patterns: patterns, 
                    dates: dates,
                    queryConditions: queryConditions
                })
            })
            .then(response => response.json())
            .then(data => {
                // 计算查询耗时
                const queryEndTime = new Date();
                const queryDuration = queryEndTime - queryStartTime;
                
                if (data.error) {
                    showMessage(data.error, 'error');
                } else if (data.success) {
                    let message = `批量查询完成！查询了 ${data.patterns_queried} 个条件，共找到 ${data.total_count} 条记录`;
                    if (data.errors && data.errors.length > 0) {
                        message += `\n部分查询失败: ${data.errors.join('; ')}`;
                    }
                    showMessage(message, 'success');
                    
                    // 显示查询时间信息
                    displayQueryTime(queryStartTime, queryEndTime, queryDuration);
                    
                    window.currentGroupedData = data.grouped_results; // 存储数据以便导出
                    displayGroupedResults(data.grouped_results);
                }
            })
            .catch(error => {
                showMessage('查询失败，请检查网络连接', 'error');
            })
            .finally(() => {
                queryBtn.disabled = false;
                queryBtn.textContent = '🔍 开始查询';
            });
        });
        
        function showMessage(message, type) {
            document.getElementById('message').innerHTML = `<div class="${type}">${message}</div>`;
        }
        
        function displayQueryTime(startTime, endTime, duration) {
            const queryTimeDiv = document.getElementById('queryTime');
            
            // 格式化时间
            const formatTime = (date) => {
                return date.toLocaleString('zh-CN', {
                    year: 'numeric',
                    month: '2-digit',
                    day: '2-digit',
                    hour: '2-digit',
                    minute: '2-digit',
                    second: '2-digit',
                    hour12: false
                });
            };
            
            // 格式化耗时
            const formatDuration = (ms) => {
                if (ms < 1000) {
                    return `${ms}毫秒`;
                } else if (ms < 60000) {
                    return `${(ms / 1000).toFixed(2)}秒`;
                } else {
                    const minutes = Math.floor(ms / 60000);
                    const seconds = ((ms % 60000) / 1000).toFixed(2);
                    return `${minutes}分${seconds}秒`;
                }
            };
            
            queryTimeDiv.innerHTML = `
                <div style="background: #f5f5f5; padding: 10px; border-radius: 5px; border-left: 4px solid #2196f3;">
                    <strong>⏰ 查询时间信息：</strong><br>
                    📅 开始时间：${formatTime(startTime)}<br>
                    📅 结束时间：${formatTime(endTime)}<br>
                    ⏱️ 查询耗时：${formatDuration(duration)}
                </div>
            `;
        }
        
        function displayGroupedResults(groupedData) {
            const groupedResultsDiv = document.getElementById('groupedResults');
            const resultsCount = document.getElementById('resultsCount');
            
            groupedResultsDiv.innerHTML = '';
            
            let totalCount = 0;
            for (const condition in groupedData) {
                totalCount += groupedData[condition].count;
            }
            resultsCount.textContent = `共找到 ${totalCount} 条记录`;

            // 遍历每个查询条件及其结果
            for (const condition in groupedData) {
                const groupData = groupedData[condition];
                if (groupData && groupData.data && groupData.data.length > 0) {
                    // 创建分组容器
                    const groupDiv = document.createElement('div');
                    groupDiv.className = 'group-container';
                    
                    // 创建分组标题（可点击折叠）
                    const headerDiv = document.createElement('div');
                    headerDiv.className = 'group-header';
                    headerDiv.innerHTML = `
                        <span class="toggle-icon">▼</span>
                        📞 查询条件: ${condition} (${groupData.count}条记录)
                    `;
                    
                    // 创建分组内容容器
                    const contentDiv = document.createElement('div');
                    contentDiv.className = 'group-content';
                    
                    // 创建表格
                    const table = document.createElement('table');
                    table.innerHTML = `
                        <thead>
                            <tr>
                                <th>ID</th>
                                <th>主叫号码</th>
                                <th>被叫号码</th>
                                <th>开始时间</th>
                                <th>结束时间</th>
                                <th>通话时长</th>
                                <th>账户号码</th>
                                <th>账户名称</th>
                                <th>主叫网关</th>
                                <th>被叫网关</th>
                            </tr>
                        </thead>
                        <tbody></tbody>
                    `;
                    
                    const tbody = table.querySelector('tbody');
                    
                    // 添加该分组的数据行
                    groupData.data.forEach(row => {
                        const tr = document.createElement('tr');
                        tr.innerHTML = `
                            <td>${row.id || ''}</td>
                            <td>${row.calleraccesse164 || ''}</td>
                            <td>${row.calleeaccesse164 || ''}</td>
                            <td>${row.starttime_beijing || ''}</td>
                            <td>${row.stoptime_beijing || ''}</td>
                            <td>${row.holdtime || ''}</td>
                            <td>${row.customeraccount || ''}</td>
                            <td>${row.customername || ''}</td>
                            <td>${row.callergatewayid || ''}</td>
                            <td>${row.calleegatewayid || ''}</td>
                        `;
                        tbody.appendChild(tr);
                    });
                    
                    contentDiv.appendChild(table);
                    
                    // 添加折叠功能
                    headerDiv.addEventListener('click', function() {
                        const isCollapsed = contentDiv.classList.contains('collapsed');
                        if (isCollapsed) {
                            contentDiv.classList.remove('collapsed');
                            headerDiv.classList.remove('collapsed');
                        } else {
                            contentDiv.classList.add('collapsed');
                            headerDiv.classList.add('collapsed');
                        }
                    });
                    
                    groupDiv.appendChild(headerDiv);
                    groupDiv.appendChild(contentDiv);
                    groupedResultsDiv.appendChild(groupDiv);
                }
            }
            
            document.getElementById('results').style.display = 'block';
        }
        
        // 展开所有分组
        function expandAllGroups() {
            const headers = document.querySelectorAll('.group-header');
            const contents = document.querySelectorAll('.group-content');
            
            headers.forEach(header => header.classList.remove('collapsed'));
            contents.forEach(content => content.classList.remove('collapsed'));
        }
        
        // 折叠所有分组
        function collapseAllGroups() {
            const headers = document.querySelectorAll('.group-header');
            const contents = document.querySelectorAll('.group-content');
            
            headers.forEach(header => header.classList.add('collapsed'));
            contents.forEach(content => content.classList.add('collapsed'));
        }
        
        // 导出CSV
        function exportToCSV() {
            const groupedData = window.currentGroupedData;
            if (!groupedData) {
                showMessage('没有数据可导出', 'error');
                return;
            }
            
            let csvContent = "ID,主叫号码,被叫号码,开始时间,结束时间,通话时长,账户号码,账户名称,主叫网关,被叫网关,查询条件\n";
            
            for (const condition in groupedData) {
                const groupData = groupedData[condition];
                if (groupData && groupData.data) {
                    groupData.data.forEach(row => {
                        csvContent += `"${row.id || ''}","${row.calleraccesse164 || ''}","${row.calleeaccesse164 || ''}","${row.starttime_beijing || ''}","${row.stoptime_beijing || ''}","${row.holdtime || ''}","${row.customeraccount || ''}","${row.customername || ''}","${row.callergatewayid || ''}","${row.calleegatewayid || ''}","${condition}"\n`;
                    });
                }
            }
            
            // 生成带时间戳的文件名
            const now = new Date();
            const timestamp = now.toISOString().slice(0,19).replace(/:/g, '-');
            const filename = `话单查询结果_${timestamp}.csv`;
            
            const blob = new Blob([csvContent], { type: 'text/csv;charset=utf-8;' });
            const link = document.createElement('a');
            const url = URL.createObjectURL(blob);
            link.setAttribute('href', url);
            link.setAttribute('download', filename);
            link.style.visibility = 'hidden';
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
            
            showMessage(`文件已导出: ${filename}`, 'success');
        }
        
        // 导出Excel (使用CSV格式，Excel可以打开)
        function exportToExcel() {
            exportToCSV(); // 暂时使用CSV格式，Excel可以打开
        }

        function showQuerySection() {
            document.getElementById('configSection').style.display = 'none';
            document.getElementById('querySection').style.display = 'block';
            document.getElementById('queryForm').reset(); // 清空查询表单
            document.getElementById('message').innerHTML = ''; // 清空消息
            document.getElementById('results').style.display = 'none'; // 隐藏结果
            document.getElementById('resultsCount').textContent = '';
            document.getElementById('queryTime').innerHTML = '';
            document.getElementById('groupedResults').innerHTML = '';
        }
    </script>
</body>
</html> 